package com.myutils;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.commons.lang.StringUtils;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;

/**
 * @author Lan
 * @date 2012-7-23
 */
public class ExcelUtil {
	/**
	 * 读取Excel文件的内容
	 * 
	 * @param file
	 *            待读取的文件
	 * @return
	 */
	public static void readPriceExcel(File file) {
		
		Workbook workbook = null;
		try {
			// 构造Workbook（工作薄）对象
			workbook = Workbook.getWorkbook(file);
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		if(workbook != null){
			Sheet sheet = workbook.getSheet(0);

			int rowNum = sheet.getRows();// 得到当前工作表的行数
			
			Document document = DocumentHelper.createDocument();
			Element rootElm = document.addElement("Message");
			rootElm.addElement("Identity").addText("PriceOfThings");
			rootElm.addElement("BigTitle").addText("上海市主副食品价格监测");
			rootElm.addElement("Exception").addAttribute("Status", "0").addText("调用成功");

			Element descElm = rootElm.addElement("Description");
			descElm.addAttribute("Count", String.valueOf(rowNum - 4));
			for(int i = 4; i < rowNum; i++){//从第五行开始读取
				Cell[] rowCells = sheet.getRow(i);// 得到当前行的所有单元格
				
				String categoryName = rowCells[0].getContents();//类别名称
				String productName = rowCells[1].getContents();//商品名称
				String specModel = rowCells[2].getContents();//规则等级
				String unit = rowCells[3].getContents();//计量单位
				String preDayPrice = rowCells[4].getContents();//上日价格
				String currDayPrice = rowCells[5].getContents();//当日价格
				String compareRate = rowCells[6].getContents();//比上日
				
				Element itemElm = descElm.addElement("Item");
				itemElm.addElement("ITitle").addText("批次号：793FBE82-9079-48BA-A99D-23105D3E46ED");
				itemElm.addElement("ReleasedTime").addText("2012-06-13 20:43");
				itemElm.addElement("Category").addText(categoryName);
				itemElm.addElement("ProductName").addText(productName);
				itemElm.addElement("SpecificationLevels").addText(specModel);
				itemElm.addElement("Unit").addText(unit);
				itemElm.addElement("PriceOfPreviousDay").addText(preDayPrice);
				itemElm.addElement("PriceOfCurrentDay").addText(currDayPrice);
				itemElm.addElement("PercentageCompareLastDay").addText(compareRate);
				itemElm.addElement("ExpandString").addText("");
				
			}
			String xmlFilePath = System.getProperty("user.dir") + "/WebRoot/WEB-INF/xml/price.xml";
			
			System.out.println(document.asXML());
			XmlUtil.outXmlLocal(document, xmlFilePath);
			
			// 最后关闭资源，释放内存
			workbook.close();
		}
	
	}
	
	public static void readIconExcel(File file){
		Workbook workbook = null;
		try {
			// 构造Workbook（工作薄）对象
			workbook = Workbook.getWorkbook(file);
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		if(workbook != null){
			Sheet sheet = workbook.getSheet(0);

			Properties prop = new Properties();
			
			int rowNum = sheet.getRows();// 得到当前工作表的行数
			for(int i = 0; i < rowNum; i++){
				Cell[] rowCells = sheet.getRow(i);// 得到当前行的所有单元格
				String weaName = rowCells[0].getContents();
				String weaIcon = "wea" + rowCells[1].getContents() + ".gif";
				prop.put(weaName, weaIcon);
			}
			
			OutputStream os = null;
			try {
				os = new FileOutputStream("D:/weaIcon.properties");
				String description = "store properties to test.properties";  
				prop.store(os, description);  
				os.close();  
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}  
		}
	}
	
	public static void renameIcon(File file){
		Workbook workbook = null;
		try {
			// 构造Workbook（工作薄）对象
			workbook = Workbook.getWorkbook(file);
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		if(workbook != null){
			Sheet sheet = workbook.getSheet(0);

			Map<String, String> iconMap = new HashMap<String, String>();
			
			int rowNum = sheet.getRows();// 得到当前工作表的行数
			for(int i = 0; i < rowNum; i++){
				Cell[] rowCells = sheet.getRow(i);// 得到当前行的所有单元格
				String weaName = rowCells[0].getContents();
				String weaIcon = "wea" + rowCells[1].getContents() + ".gif";
				iconMap.put(weaName, weaIcon);
			}
			
			File[] files = FileUtil.getFilesOfDir("D:\\icon");
			for(File iconFile : files){
				String oldpath = iconFile.getAbsolutePath();
				String name = FileUtil.getBaseName(oldpath);
				String newname = iconMap.get(name);
				if(StringUtils.isBlank(newname)){
					System.out.println(name + "对应的图片不存在");
				}else{
					String newpath = FileUtil.getFullPath(oldpath) + newname;
					File dest = new File(newpath);
					
					if(iconFile.renameTo(dest)){
						System.out.println(oldpath + " -> " + newpath);
					}else{
						System.out.println(oldpath + "重命名失败");
					}
				}
			}
			
		}
	}
	
	public static void main(String[] args) {
//		readPriceExcel(new File("D:/test.xls"));
		
//		readIconExcel(new File("D:/weaIcon.xls"));
		
		renameIcon(new File("D:/weaIcon.xls"));
		
	}
}
